'''
Created on Dec 14, 2011

Purpose:Take a large array, and a corresponding title array, and create a mySQL create statement to transfer it into an
SQL database. 

Why?: Modifying the python scripts/ using excel on the .csv is extremely time consuming for collecing simple attributes of
subcollections of the data. mySQL databases are a whole lot quicker. 

Use: Instantiate, call single method with critical data in params (titles(column names[]),data([][])
Optional: 
Name(Str) Name of created table, default("defaultTable")
PKI(int) Index of data to be used as primary key, default(-1)
@author: Box II

CREATE TABLE `redacted_war_diary_irq` (
  `id` int(11) NOT NULL auto_increment,
  `url` char(22) NOT NULL default '',
  `reportkey` char(255) NOT NULL default '',
  `date` datetime default NULL,
  `type` char(255) default NULL,
  `category` char(255) default NULL,
  `trackingnumber` char(255) default NULL,
  `title` text,
  
  
'''

class pySQLExport():
    fileIO=open("sqlOutput.sql","w")

    def __init__(self):
        pass
    
        
   
    '''DATA CONVERSION'''   
    def retrievePunctuation(self,types):
        returnArray=[]
        for i in types:
            if i.find("FLOAT")>=0 or i.find("INT")>=0:
                returnArray.append("")
            else:
                returnArray.append("'")
        return returnArray
            
    
    def readDataType(self,var):
        '''
        0:Int
        1:Float
        2:DateTime
        3:Varchar
        4:Text
        '''
        var=var.rstrip(" ")
        var=var.lstrip(" ")
        resType="VARCHAR(255)"
        try:
            if(int(float(var))!=float(var)):
                resType="FLOAT("+str(len(var))+","+str(len(var.split(".")[1]))+")"
            else:
                resType="INT(11)"
                
        except:
            if(len(var)>255):
                resType="TEXT"
            else:
                resType="VARCHAR(255)"       
            if (var.find("-") >=0 or var.find("\\") >=0 or var.find("/") >=0) and var.find(":") >=0 :              
                resType="DATETIME"
        return resType
                
    def collectDataTypes(self,dataArray):
        tempArray=[]
        for i in dataArray:
            tempArray.append(self.readDataType(i))
        return tempArray
            
    '''PRIMARIES'''
    def writeInserts(self,types,data,name,titles):
        print "Writing Inserts."
        punctuation=self.retrievePunctuation(types)
        blockSize=1000
        breakEnable=0
        for i in range(0,len(data)-1): 
            if(i>-2):#Testing...
                if(breakEnable==1):
                    self.writeInsertHeaders(name, titles)
                    breakEnable=0
                self.fileIO.write("SELECT ")
                varStr=""
                for r in range(0,len(data[i])-1):
                    varStr= varStr+str(punctuation[r])+""+str(data[i][r])+""+str(punctuation[r])+","
                varStr=varStr.rstrip(",")
                self.fileIO.write(varStr+"\n")
                if((i-1)%blockSize == 0 and i>2):
                    print "Made Block: "+str((i-1)/blockSize)
                    breakEnable=1
                    self.fileIO.write(";\n")
                if(i != len(data)-2 and breakEnable != 1):
                    self.fileIO.write("UNION ALL\n")
                    
                    
    def writeInsertHeaders(self,name,titles):
        self.fileIO.write("INSERT INTO `"+str(name.lower())+"`(\n")
        for i in range(0,len(titles)-1):
                self.fileIO.write("`"+str(titles[i].replace(" ","_"))+"`")
                if(i!=len(titles)-2):
                    self.fileIO.write(",")
                self.fileIO.write("\n")  
        self.fileIO.write(")\n")      
        
    def writeCreateAndInsHeaders(self,name,titles,types,PKI,mydata):
        
        print "Writing headers"
        self.fileIO.write("CREATE TABLE `"+str(name.lower())+"` (\n")
        for i in range(0,len(titles)-1):
            if(i==PKI):
                self.fileIO.write("`"+str(titles[i].replace(" ","_"))+"` "+str(types[i])+ " NOT NULL auto_increment")
            else:
                self.fileIO.write("`"+str(titles[i].replace(" ","_"))+"` "+str(types[i])+ " NOT NULL")
            if i != len(titles)-2:
                self.fileIO.write(",")
            self.fileIO.write("\n")
        self.fileIO.write(")\n")
        self.fileIO.write(";\n")
        self.writeInsertHeaders(name,titles)
        self.writeInserts(types, mydata,name,titles)
       
            
                
                        
    def saveArrayAsSQL(self,titleArray,dataArray,PKI=-1,name="defaultTable"):
        result=""
        if len(titleArray) != len(dataArray[0]):
            result=result+" ERR 1: Length of Title array and data array differ: \nTITLE(LEN)="+str(len(titleArray))+" \nDATA(LEN)="+str(len(dataArray))+"\n"
        else:
            if PKI > -1:
                if PKI > len(titleArray-1):
                    typeArray=self.collectDataTypes(dataArray[0])#Types.
                    self.fileIO = open("sqlOutput.sql","w")#set to write. 
                    self.writeCreateAndInsHeaders(name, titleArray, typeArray, PKI,dataArray)
                    
                    
                    
                else:
                    result=result+" ERR 3: Primary Key specification not in domain of title array: \nTITLE(LEN)="+str(len(titleArray))+" \nPK(INDEX)="+str(PKI)+"\n"
            else:
                result=result+" WARNING 1: No primary key index specified, adding column 'MYPK' as key storage.  \n"
                typeArray=self.collectDataTypes(dataArray[0])#Types.
                self.fileIO = open("sqlOutput.sql","w")#set to write. 
                self.writeCreateAndInsHeaders(name, titleArray, typeArray, PKI,dataArray)
        self.fileIO.close()
        return result
    
    
    

            
            
            
        